package com.yunfeisoft.business.dao.impl.postgres;

import com.applet.base.ServiceDaoImpl;
import com.applet.sql.builder.SelectBuilder;
import com.applet.sql.builder.WhereBuilder;
import com.applet.sql.mapper.DefaultRowMapper;
import com.applet.utils.DateUtils;
import com.applet.utils.Page;
import com.yunfeisoft.business.dao.inter.SaleOrderDao;
import com.yunfeisoft.business.model.Customer;
import com.yunfeisoft.business.model.SaleOrder;
import com.yunfeisoft.business.model.SaleOrderStatistics;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * ClassName: SaleOrderDaoImpl
 * Description: 销售单信息Dao实现
 * Author: Jackie liu
 * Date: 2020-07-23
 */
@Repository
public class SaleOrderDaoImpl extends ServiceDaoImpl<SaleOrder, String> implements SaleOrderDao {

    @Override
    public Page<SaleOrder> queryPage(Map<String, Object> params) {
        WhereBuilder wb = new WhereBuilder();
        wb.setOrderByWithDesc("so.createTime");
        if (params != null) {
            initPageParam(wb, params);
            wb.andEquals("so.orgId", params.get("orgId"));
            wb.andEquals("so.payStatus", params.get("payStatus"));
            wb.andFullLike("c.name", params.get("customerName"));
        }

        SelectBuilder builder = getSelectBuilder("so");
        builder.column("c.name as customerName")
                .join(Customer.class).alias("c").on("so.customerId = c.id");

        return queryPage(builder.getSql(), wb);
    }

    @Override
    public List<SaleOrder> queryByIds(String[] ids) {
        if (ArrayUtils.isEmpty(ids)) {
            return new ArrayList<>();
        }

        WhereBuilder wb = new WhereBuilder();
        wb.andIn("id", ids);
        return query(wb);
    }

    @Override
    public List<SaleOrderStatistics> queryStatisticsByCustomer(String orgId, String beginDate, String endDate) {
        String sql = "SELECT SO.CUSTOMER_ID_ AS ID_, C.NAME_ AS NAME_, SUM (SO.TOTAL_AMOUNT_) AS TOTAL_AMOUNT_, SUM (SO.TOTAL_COST_AMOUNT_) AS TOTAL_COST_AMOUNT_ " +
                "FROM TT_SALE_ORDER SO JOIN TT_CUSTOMER C ON SO.CUSTOMER_ID_ = C.ID_ WHERE SO.IS_DEL_ = 2 AND SO.STATUS_ = ? AND SO.ORG_ID_ = ? AND SO.SALE_DATE_ >= ? AND SO.SALE_DATE_ <= ? GROUP BY SO.CUSTOMER_ID_, C.NAME_";
        return jdbcTemplate.query(sql, new Object[]{SaleOrder.SaleOrderStatusEnum.DELIVERED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<SaleOrderStatistics>(SaleOrderStatistics.class));
    }

    @Override
    public List<SaleOrderStatistics> queryStatisticsByProductCategory(String orgId, String beginDate, String endDate) {
        String sql = "SELECT P.CATEGORY_ID_ AS ID_, PC.NAME_ AS NAME_, SUM(SI.SALE_AMOUNT_) AS TOTAL_AMOUNT_, SUM(SI.AMOUNT_) AS TOTAL_COST_AMOUNT_ FROM TT_SALE_ITEM SI " +
                "JOIN TT_PRODUCT P ON SI.PRODUCT_ID_ = P.ID_ JOIN TT_PRODUCT_CATEGORY PC ON P.CATEGORY_ID_ = PC.ID_ JOIN TT_SALE_ORDER SO ON SI.SALE_ORDER_ID_ = SO.ID_ " +
                "WHERE SI.IS_DEL_ = 2 AND SO.STATUS_ = ? AND SI.ORG_ID_ = ? AND SO.SALE_DATE_ >= ? AND SO.SALE_DATE_ <= ? GROUP BY P.CATEGORY_ID_, PC.NAME_";
        return jdbcTemplate.query(sql, new Object[]{SaleOrder.SaleOrderStatusEnum.DELIVERED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<SaleOrderStatistics>(SaleOrderStatistics.class));
    }

    @Override
    public List<SaleOrderStatistics> queryStatisticsByProduct(String orgId, String beginDate, String endDate) {
        String sql = "SELECT P.ID_ AS ID_, P.NAME_ AS NAME_, SUM(SI.SALE_AMOUNT_) AS TOTAL_AMOUNT_, SUM (SI.AMOUNT_) AS TOTAL_COST_AMOUNT_ " +
                "FROM TT_SALE_ITEM SI JOIN TT_PRODUCT P ON SI.PRODUCT_ID_ = P.ID_ JOIN TT_SALE_ORDER SO ON SI.SALE_ORDER_ID_ = SO.ID_ " +
                "WHERE SI.IS_DEL_ = 2 AND SO.STATUS_ = ? AND SI.ORG_ID_ = ? AND SO.SALE_DATE_ >= ? AND SO.SALE_DATE_ <= ? GROUP BY P.ID_, P.NAME_";
        return jdbcTemplate.query(sql, new Object[]{SaleOrder.SaleOrderStatusEnum.DELIVERED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<SaleOrderStatistics>(SaleOrderStatistics.class));
    }

    @Override
    public List<SaleOrderStatistics> queryStatisticsBySupplier(String orgId, String beginDate, String endDate) {
        String sql = "SELECT P.SUPPLIER_ID_ AS ID_, S.NAME_ AS NAME_, SUM(SI.SALE_AMOUNT_) AS TOTAL_AMOUNT_, SUM(SI.AMOUNT_) AS TOTAL_COST_AMOUNT_ " +
                "FROM TT_SALE_ITEM SI JOIN TT_PRODUCT P ON SI.PRODUCT_ID_ = P.ID_ JOIN TT_SUPPLIER S ON P.SUPPLIER_ID_ = S.ID_ JOIN TT_SALE_ORDER SO ON SI.SALE_ORDER_ID_ = SO.ID_ " +
                "WHERE SI.IS_DEL_ = 2 AND SO.STATUS_ = ? AND SI.ORG_ID_ = ? AND SO.SALE_DATE_ >= ? AND SO.SALE_DATE_ <= ? GROUP BY P.SUPPLIER_ID_, S.NAME_";
        return jdbcTemplate.query(sql, new Object[]{SaleOrder.SaleOrderStatusEnum.DELIVERED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<SaleOrderStatistics>(SaleOrderStatistics.class));
    }

    @Override
    public List<SaleOrderStatistics> queryStatisticsByCreateId(String orgId, String beginDate, String endDate) {
        String sql = "SELECT SO.CREATE_ID_, U.NAME_, SUM(SO.TOTAL_AMOUNT_) AS TOTAL_AMOUNT_, SUM(SO.TOTAL_COST_AMOUNT_) AS TOTAL_COST_AMOUNT_ " +
                "FROM TT_SALE_ORDER SO JOIN TS_USER U ON SO.CREATE_ID_ = U.ID_ WHERE SO.IS_DEL_ = 2 AND SO.STATUS_ = ? AND SO.ORG_ID_ = ? AND SO.SALE_DATE_ >= ? AND SO.SALE_DATE_ <= ? GROUP BY SO.CREATE_ID_, U.NAME_";
        return jdbcTemplate.query(sql, new Object[]{SaleOrder.SaleOrderStatusEnum.DELIVERED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<SaleOrderStatistics>(SaleOrderStatistics.class));
    }

    @Override
    public List<SaleOrderStatistics> queryStatisticsByDate(String orgId, String beginDate, String endDate) {
        String sql = "SELECT TO_CHAR(SALE_DATE_, 'YYYY-MM-DD') AS DAY_, COUNT(1) AS SALE_NUM_, SUM(TOTAL_AMOUNT_) AS TOTAL_AMOUNT_ FROM TT_SALE_ORDER WHERE IS_DEL_ = 2 AND STATUS_ = ? AND ORG_ID_ = ? AND SALE_DATE_ >= ? AND SALE_DATE_ <= ? GROUP BY DAY_";
        return jdbcTemplate.query(sql, new Object[]{SaleOrder.SaleOrderStatusEnum.DELIVERED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<SaleOrderStatistics>(SaleOrderStatistics.class));
    }

    @Override
    public SaleOrder queryTotalAmount(String orgId, int status, int payStatus) {
        String sql = "SELECT SUM(TOTAL_AMOUNT_) AS TOTAL_AMOUNT_, SUM(PAY_AMOUNT_) AS PAY_AMOUNT_ FROM TT_SALE_ORDER WHERE ORG_ID_ = ? AND STATUS_ = ? AND PAY_STATUS_ = ? AND IS_DEL_ = 2";
        List<SaleOrder> list = jdbcTemplate.query(sql, new Object[]{orgId, status, payStatus}, new DefaultRowMapper<SaleOrder>(SaleOrder.class));
        return CollectionUtils.isEmpty(list) ? new SaleOrder() : list.get(0);
    }
}